//
//  DBValueSuper.m
//  CareRingApp
//
//  Created by Linktop on 2022/6/7.
//

#import "DBValueSuper.h"
#import "TimeUtils.h"

@implementation DBValueSuper
//+(void)createTable {
//    // 创建表
//    NSAssert(YES, @"%s need be overrite by subclass", __func__);
//}

-(void)insert:(void(^ _Nullable)(BOOL succ))complete {
    NSAssert(YES, @"%s need be overrite by subclass", __func__);
}

-(instancetype)initWith:(NSDate *)time Value:(NSNumber *)value UserId:(NSNumber *)userId {
    
    if (self = [super init]) {
        self.time = time;
        self.value = value;
//        self.userId = userId;
    }
    
    return self;
}

-(instancetype)initWith:(NSDate *)time Value:(NSNumber *)value Account:(NSString *)account
{
    if (self = [super init]) {
        self.time = time;
        self.value = value;
        self.macAddress = account;
    }
    
    return self;
}


//创建数据库
+(void)createTable:(NSString *)tableName
{
    
    [[HMDBManager instance].dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        //创建数据库表
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(userId INT, time DATETIME, value FLOAT, account TEXT NOT NULL)", tableName];
        bool succ = [db executeUpdate:sql];
        NSLog(@"create table %@ result =%d ", tableName, succ);
        
    }];
    
}





/// 查询时间段内平均值
/// @param account 账户
/// @param beginDate 开始时间
/// @param endDate 结束时间
/// @param cmpBlk 完成
+(void)queryAverage:(NSString * _Nonnull)account
              Begin:(NSDate *)beginDate End:(NSDate *)endDate
          TableName:(NSString *)tableName
           Cpmplete:(void(^)(NSNumber * _Nullable average, NSNumber * _Nullable maxTime,  NSNumber * _Nullable minTime ) )cmpBlk
{
    if (!account.length) {
        return;
    }
    
    [[HMDBManager instance].dbQueue inDatabase:^(FMDatabase * _Nonnull db) {

//        NSMutableArray<DBAccount *> *results = [NSMutableArray new];
        NSString *sql =  [NSString stringWithFormat:@"SELECT AVG(value) as avgValue, MAX(time) as maxTime, MIN(time) as minTime FROM %@ WHERE account = ? AND time BETWEEN ? AND ? ", tableName];
        FMResultSet *rs = [db executeQuery:sql, account, beginDate, endDate];
        NSNumber *avg = 0;
        NSNumber *maxTIme = nil;
        NSNumber *minTIme = nil;

        while ([rs next]) {
            avg = @([rs doubleForColumn:@"avgValue"]);
            maxTIme = @([rs doubleForColumn:@"maxTime"]);

            minTIme = @([rs doubleForColumn:@"minTime"]);

//            DBAccount *accountModel = [[DBAccount alloc]init];
//            accountModel.userId = @([rs intForColumn:@"userId"]);
//            accountModel.account = [rs stringForColumn:@"account"];
//            accountModel.pwd = [rs stringForColumn:@"pwd"];
//            accountModel.cId = [rs intForColumn:@"rowid"];
//
//
//            [results addObject:accountModel];
        }
        [rs close];
       
        if (cmpBlk) {
            dispatch_async(dispatch_get_main_queue(), ^{
                cmpBlk(avg, maxTIme, minTIme);
            });
        }
       
    }];
}

// 按日内小时查询数据 (每小时最大,最小,平均值)
+(void)queryMutiDataGroupHours:(NSString * _Nonnull)account DayDate:(NSDate *)dayDate  TableName:(NSString *)tableName Cpmplete:(void(^)(NSMutableArray<NSDictionary *> * res) )cmpBlk
{
    if (!account.length) {
        return;
    }
    
    [[HMDBManager instance].dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        
        NSDate *zeroDate = [TimeUtils zeroOfDate:dayDate];
        NSTimeInterval zeroInterval = [zeroDate timeIntervalSince1970];
        NSInteger oneHourSecs = 3600;
        NSMutableArray<NSDictionary *> * res = [NSMutableArray new];
        
       
        
        for (int i  = 0; i < 24; i++) {
            NSDate *beginDate = [NSDate dateWithTimeIntervalSince1970:zeroInterval + i * oneHourSecs];
            NSDate *endDate = [NSDate dateWithTimeIntervalSince1970:zeroInterval + (i+1) * oneHourSecs];
            
            NSString *sqlCnt =  [NSString stringWithFormat:@"SELECT COUNT(*) AS cnt FROM %@ WHERE account = ? AND time BETWEEN ? AND ? ", tableName];
            FMResultSet *rsCnt = [db executeQuery:sqlCnt, account, beginDate, endDate];
            int count = 0;
            while ([rsCnt next]) {
                count = [rsCnt intForColumn:@"cnt"];
              
            }
            [rsCnt close];
            
            if (count == 0) {
                continue;
            }

            NSString *sql =  [NSString stringWithFormat:@"SELECT AVG(value) as avgValue, MAX(value) as maxValue, MIN(value) as minValue FROM %@ WHERE account = ? AND time BETWEEN ? AND ? ", tableName];
            FMResultSet *rs = [db executeQuery:sql, account, beginDate, endDate];
            float avg = 0;
            float max, min;
            while ([rs next]) {
                avg = [rs doubleForColumn:@"avgValue"];
                max = [rs doubleForColumn:@"maxValue"];
                min = [rs doubleForColumn:@"minValue"];
                NSDictionary *dict = @{@"min" : @(min),
                                       @"max" : @(max),
                                       @"avg":@(avg),
                                       @"hour" : @(i) };
                [res addObject:dict];
                break;
            }
            [rs close];
        }
        
//        NSMutableArray<DBAccount *> *results = [NSMutableArray new];
       
       
        if (cmpBlk) {
            dispatch_async(dispatch_get_main_queue(), ^{
                cmpBlk(res);
            });
        }
       
    }];
}



/// 查询账号关联的数据的最大时间戳
/// @param account 账号
/// @param tableName 表名
/// @param cmpBlk 回调
+(void)queryMaxTime:(NSString * _Nonnull)account TableName:(NSString *)tableName Cpmplete:(void(^)(NSNumber * _Nullable maxTimeStamp) )cmpBlk
{
    if (!account.length) {
        return;
    }
    
    [[HMDBManager instance].dbQueue inDatabase:^(FMDatabase * _Nonnull db) {

//        NSMutableArray<DBAccount *> *results = [NSMutableArray new];
        NSString *sql =  [NSString stringWithFormat:@"SELECT MAX(time) as maxTime FROM %@ WHERE account = ? ", tableName];
        FMResultSet *rs = [db executeQuery:sql, account];
        NSNumber * maxTimeStamp = nil;
        while ([rs next]) {
            maxTimeStamp = @([rs doubleForColumn:@"maxTime"]);
        }
        [rs close];
       
        if (cmpBlk) {
            dispatch_async(dispatch_get_main_queue(), ^{
                cmpBlk(maxTimeStamp);
            });
        }
       
    }];
}


+(void)deleteAll:(NSString *)tableName
{
    [[HMDBManager instance].dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        //创建数据库表
        NSString *sql = [NSString stringWithFormat:@"DELETE FORM %@", tableName];
        bool succ = [db executeUpdate:sql];
        NSLog(@"delete data from %@ result =%d ", tableName, succ);
        
    }];
}

+(void)deleteAll{
    NSAssert(YES, @"%s need be overrite by subclass", __func__);

}

@end
